/*
 * Created on 2004-01-07
 */
package org.opencube.data;

import info.fingo.db.pool.PoolingDataSource;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.opencube.util.Constants;
import org.opencube.util.Parameter;
import org.opencube.util.ParameterMetaData;
import org.opencube.util.services.logging.StatementLogger;

/**
 * <p>The database <code>DataSource</code> implementation and extension. 
 * Here, most of the 
 * 
 * @author <a href="mailto:maciek@fingo.pl">FINGO - Maciej Mroczko</a>
 */
public abstract class Database extends org.opencube.data.DataSource
{
	private Logger logger = Logger.getLogger( Database.class );
	/* The auto commit flag */
	private boolean autoCommit = false;
	/* The actually used db-connection */
	private Connection connection = null;
	/* Data source */
	private DataSource dataSource = null;
	
	/* The data source properties */
	protected Properties databaseProperties = null;
	
	public Database()
	{
	}
	
	public Database( DataSource dataSource )
	{
		this.dataSource = dataSource;
	}
	/*
	 * (non-Javadoc)
	 * @see org.opencube.oms.core.DataSource#connect(java.util.Properties)
	 */	
	public void connect( Properties properties ) throws Exception
	{
		super.connect( properties );
		this.databaseProperties = buildDbProperties( properties );
		
		this.dataSource = new PoolingDataSource( this.databaseProperties );
	}

	/*
	 * (non-Javadoc)
	 * @see org.opencube.oms.core.DataSource#isAutoCommit()
	 */
	public boolean isAutoCommit()
	{
		return this.autoCommit;
	}

	/*
	 * (non-Javadoc)
	 * @see org.opencube.oms.core.DataSource#setAutoCommit(boolean)
	 */
	public void setAutoCommit( boolean autoCommit ) throws SQLException
	{
		if( this.autoCommit != autoCommit )
		{
			if( !this.autoCommit )
			{
				rollback();
			}
			this.autoCommit = autoCommit;
		}
	}

	/*
	 * (non-Javadoc)
	 * @see org.opencube.oms.core.DataSource#commit()
	 */
	public void commit() throws SQLException
	{
		try
		{
			if( this.connection != null )
			{
				this.connection.commit();
			}
		}
		finally
		{
			releaseConnection( true );
		}
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.opencube.oms.core.DataSource#rollback()
	 */
	public void rollback() throws SQLException
	{
		try
		{
			if( this.connection != null )
			{
				this.connection.rollback();
			}
		}
		finally
		{
			releaseConnection( true );
		}
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.opencube.oms.Database#executeSQL(java.lang.String)
	 */
	public boolean execute( String statement ) throws SQLException
	{
		return execute( statement, null, null );
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.opencube.oms.core.DataSource#executeUpdate(java.lang.String)
	 */
	public int executeUpdate( String statement ) throws SQLException
	{
		return executeUpdate( statement, null, null );
	}

	/*
	 * (non-Javadoc)
	 * @see org.opencube.oms.Database#executeQuery(java.lang.String)
	 */
	public RecordSet executeQuery( String query ) throws SQLException
	{
		return executeQuery( query, null, null );
	}
	
	protected void bindParameters( CallableStatement statement, ParameterMetaData metaData, BoundVariable[] vars )
	throws SQLException
	{
		bindInputParameters( statement, metaData );
		registerOutputParameters( statement, metaData );
	}
	
	protected void bindInputParameters( PreparedStatement statement, ParameterMetaData metaData )
	throws SQLException
	{
		// bind input parameter
		Parameter[] inParams = metaData.getInputParameters(); 			
		for( int i = 0; inParams != null && i < inParams.length; i++ )
		{
			Parameter param = inParams[ i ];
			for( int j = 0; param.getIndexes() != null && j < param.getIndexes().length; j++ )
			{
				bindInputParameter( statement, param.getType(), param.getIndexes()[ j ], param.getValue() );			
			}
		}
	}

	protected void registerOutputParameters( CallableStatement statement, ParameterMetaData metaData )
	throws SQLException
	{
		// register output parameter
		Parameter[] outParams = metaData.getOutputParameters(); 			
		for( int i = 0; outParams != null && i < outParams.length; i++ )
		{
			Parameter param = outParams[ i ];
			for( int j = 0; param.getIndexes() != null && j < param.getIndexes().length; j++ )
			{
				registerOutputParameter( statement, param.getType(), param.getIndexes()[ j ] );
			}
		}
	}

	/*
	 * (non-Javadoc)
	 * @see org.opencube.oms.core.DataSource#executeCall(java.lang.String, java.util.HashMap)
	 */
	public CallResult executeCall( String statement, BoundVariable[] bindVariables ) throws SQLException
	{
		return executeCall( statement, bindVariables, null, null );
	}
	
	/**
	 * Fetches from the output parameters of the given callable statement 
	 * record set from the specified index.
	 * 
	 * @param cs the callable statement to fetch the result set from
	 * @param index the index of the result set to fetch
	 * 
	 * @return Record set from the output parameters from the given index.
	 * 
	 * @throws SQLException
	 */
	protected abstract RecordSet fetchRecordSet( CallableStatement cs, int index ) throws SQLException;

	/**
	 * Binds the given value for the parameter on the specified index.
	 * 
	 * @param ps the prepared statement to bind
	 * @param type the type name of the parameter
	 * @param index the index of the parameter
	 * @param value the parameter value
	 * 
	 * @throws SQLException
	 */
	protected abstract void bindInputParameter( PreparedStatement ps, String type, int index, Object value ) throws SQLException;

	/**
	 * Registers output parameter.
	 * 
	 * @param cs the callable statement to registers outputs for
	 * @param type the type of the output parameter
	 * @param index the index of the parameter to register
	 * 
	 * @throws SQLException
	 */
	protected abstract void registerOutputParameter( CallableStatement cs, String type, int index ) throws SQLException;
	
	/**
	 * Returns the jdbc driver class name
	 * 
	 * @return Driver name
	 */
	protected abstract String getDriverName();
	
	/**
	 * Return the query which may be used for testing connection
	 * 
	 * @return The test query
	 */
	protected abstract String getTestQuery();
	
	/**
	 * Fetches the given record set as the array of hash maps.
	 * 
	 * @param rs the result set to fetch
	 * 
	 * @return The record set as the array of hash maps
	 * 
	 * @throws SQLException
	 */
	protected abstract RecordSet fetchRecordSet( ResultSet rs ) throws SQLException;

	/**
	 * Returns the connection. If there is a running transaction it will return
	 * the used object, otherwise it will obtain a new one from the ConnectionManager.
	 * 
	 * The autocommit flag depends on current transactional state (negated transaction flag)
	 * 
	 * @return Connection The open connection.
	 */
	public Connection getConnection() throws SQLException
	{
			if( this.connection == null )
			{
				this.connection = this.dataSource.getConnection();
				this.connection.rollback();
				this.connection.setAutoCommit( this.autoCommit );
			}
			return this.connection;
	}
	
	/**
	 * @param alwaysRelease
	 * If there is no transaction set ( autoCommit = true ) or the 
	 * alwaysRelease flag is <code>true</code>, releases the connection. 
	 */
	protected void releaseConnection( boolean alwaysRelease )
	{
		if( this.autoCommit || alwaysRelease )
		{
			try
			{
				if( this.connection != null )
				{
					this.connection.close();
				}
			}
			catch( SQLException e )
			{
				logger.error( "Exception while closing connection", e );
			}
			this.connection = null;
		}
	}

	/**
	 * This overwritten method will return same code for the same db-url and db-username combination.
	 * 
	 * @return int The hash code for the database url and user name combination
	 */
	public int hashCode()
	{
		return ( this.url + Constants.STR_COLON + this.username ).hashCode();
	}
	
	/* Maps the given properties into the standard connection manager properties ( puts values under it's keys ) */
	private Properties buildDbProperties( Properties props )
	{
		Properties result = new Properties();
		result.put( PoolingDataSource.PROPERTY_DB_PASSWORD, props.getProperty( org.opencube.data.DataSource.PROPERTY_PASSWORD ) );
		result.put( PoolingDataSource.PROPERTY_DB_URL, props.getProperty( org.opencube.data.DataSource.PROPERTY_URL ) );
		result.put( PoolingDataSource.PROPERTY_DB_USER, props.getProperty( org.opencube.data.DataSource.PROPERTY_USERNAME ) );
		result.put( PoolingDataSource.PROPERTY_DRIVER_NAME, getDriverName() );
		result.put( PoolingDataSource.PROPERTY_CONNECTION_TEST_QUERY, getTestQuery() );

		setOptionalProperty( props, result, PoolingDataSource.PROPERTY_CONNECTION_EXPIRE_TIME );
		setOptionalProperty( props, result, PoolingDataSource.PROPERTY_ALL_CONNECTIONS_LIMIT );
		setOptionalProperty( props, result, PoolingDataSource.PROPERTY_CONNECTION_WAIT_TIMEOUT );
		setOptionalProperty( props, result, PoolingDataSource.PROPERTY_INITIAL_CONNECTION_COUNT );
		setOptionalProperty( props, result, PoolingDataSource.PROPERTY_MAX_CONNECTION_COUNT );
		setOptionalProperty( props, result, PoolingDataSource.PROPERTY_CONNECTION_IDLE_TIME );
		
		return result;
	}
	
	private void setOptionalProperty( Properties source, Properties target, String key )
	{
		if( source.getProperty( key ) != null )
		{
			target.setProperty( key, source.getProperty( key ) );
		}
	}
	
	/* Converts the sql meta data into a simplified meta data */
	protected RecordSetMetaData getMetaData( ResultSetMetaData rsmd ) throws SQLException
	{
		RecordSetMetaData metaData = new RecordSetMetaData();
		for( int i = 1; i <= rsmd.getColumnCount(); i++ )
		{
			metaData.addColumn( new Column( rsmd.getColumnName( i ) ) );  
		}
		
		return metaData;
	}
	
	/* Returns the bound variable with the given name */
	private BoundVariable getBoundVariable( BoundVariable[] bindVariables, String name )
	{
		for( int i = 0; bindVariables != null && i < bindVariables.length; i++ )
		{
			if( name.equals( bindVariables[ i ].getName() ) )
			{
				return bindVariables[ i ];
			}
		}
		
		return null;
	}

	/* (non-Javadoc)
	 * @see org.opencube.data.DataSource#execute(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public boolean execute( String statement, String namespace, String resourceKey ) throws SQLException
	{
		try
		{
			// parse
			long parseDuration = System.nanoTime();  
			Statement st = getConnection().createStatement();
			parseDuration = System.nanoTime() - parseDuration;
			
			// execute
			long executionDuration = System.nanoTime();
			boolean result = st.execute( statement );
			executionDuration = System.nanoTime() - executionDuration;
			st.close();
			
			// log statement
			StatementLogger.logStatement( namespace, resourceKey, statement, null, executionDuration, parseDuration );
			
			return result;
		}
		finally
		{
			releaseConnection( false );
		}
	}

	/* (non-Javadoc)
	 * @see org.opencube.data.DataSource#executeCall(java.lang.String, org.opencube.data.BoundVariable[], java.lang.String, java.lang.String)
	 */
	@Override
	public CallResult executeCall( String statement, BoundVariable[] bindVariables, String namespace, String resourceKey ) throws SQLException
	{
		try
		{
			// parse
			long parseDuration = System.nanoTime();  
			CallableStatement cs = getConnection().prepareCall( statement );
			
			// create meta data for parameters
			ParameterMetaData metaData = new ParameterMetaData( statement, bindVariables );
			
			// bind parameters
			bindParameters( cs, metaData, bindVariables );
			parseDuration = System.nanoTime() - parseDuration;
			
			// execute statement
			long executionDuration = System.nanoTime();  
			cs.execute();
			executionDuration = System.nanoTime() - executionDuration;  

			// fetch values for output parameters
			long fetchDuration = System.nanoTime();
			Parameter[] outParams = metaData.getOutputParameters(); 			
			CallResult result = new CallResult();
			for( int i = 0; outParams != null && i < outParams.length; i++ )
			{
				Parameter param = outParams[ i ];
				BoundVariable var = getBoundVariable( bindVariables, param.getName() );
				if( var == null )
				{
					continue;
				}
				if( TYPE_BOOLEAN.equals( param.getType() ) )
				{
					var.setValue( new Boolean( cs.getBoolean( param.getIndexes()[ 0 ] ) ) );
				}
				else if( TYPE_CURSOR.equals( param.getType() ) )
				{
					RecordSet maps = fetchRecordSet( cs, param.getIndexes()[ 0 ] );
					result.addRecordSet( maps );
					var.setValue( maps );
				}
				else if( TYPE_STRING.equals( param.getType() ) )
				{
					var.setValue( cs.getString( param.getIndexes()[ 0 ] ) );
				}
				else if( TYPE_INTEGER.equals( param.getType() ) )
				{
					var.setValue( new Integer( cs.getInt( param.getIndexes()[ 0 ] ) ) );
				}
				
				result.addBoundVariable( var );
			}
			fetchDuration = System.nanoTime() - fetchDuration;
			
			// close statement
			cs.close();
			
			// log statement
			StatementLogger.logStatement( namespace, resourceKey, statement, bindVariables, executionDuration, parseDuration, fetchDuration );

			return result;
		}
		finally
		{
			releaseConnection( false );
		}
	}

	/* (non-Javadoc)
	 * @see org.opencube.data.DataSource#executeQuery(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public RecordSet executeQuery( String query, String namespace, String resourceKey ) throws SQLException
	{
		try
		{
			// parse
			long parseDuration = System.nanoTime();
			Statement st = getConnection().createStatement();
			parseDuration = System.nanoTime() - parseDuration;
			
			// execute
			long executeDuration = System.nanoTime();
			ResultSet rs = st.executeQuery( query );
			executeDuration = System.nanoTime() - executeDuration;
			
			// fetch
			long fetchDuration = System.nanoTime();
			RecordSet result = fetchRecordSet( rs );
			fetchDuration = System.nanoTime() - fetchDuration;
			
			rs.close();
			st.close();
			
			// log statement
			StatementLogger.logStatement( namespace, resourceKey, query, null, executeDuration, parseDuration, fetchDuration );
			
			return result;
		}
		finally
		{
			releaseConnection( false );
		}
	}

	/* (non-Javadoc)
	 * @see org.opencube.data.DataSource#executeUpdate(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public int executeUpdate( String statement, String namespace, String resourceKey ) throws SQLException
	{
		try
		{
			// parse 
			long parseDuration = System.nanoTime();  
			Statement st = getConnection().createStatement();
			parseDuration = System.nanoTime() - parseDuration;
			
			// execution
			long executionDuration = System.nanoTime();  
			int result = st.executeUpdate( statement );
			executionDuration = System.nanoTime() - executionDuration;
			st.close();
				
			// log statement
			StatementLogger.logStatement( namespace, resourceKey, statement, null, executionDuration, parseDuration );

			return result;
		}
		finally
		{
			releaseConnection( false );
		}
	}
	
	
}
